package com.leo.tool.jdk.jdbc;

import lombok.SneakyThrows;
import org.h2.tools.Server;
import org.junit.Before;
import org.junit.Test;

import java.sql.*;

import static org.junit.Assert.assertEquals;

public class NativeJDBCTest {

    @Before
    public void setup() {
        new Thread(this::setupH2).start();
    }

    @SneakyThrows
    private void setupH2() {
        new Server().runTool(new String[]{});
    }

    @Test
    public void base() throws Exception {
        Class.forName("org.h2.Driver");
        Connection con = DriverManager.getConnection("jdbc:h2:mem:test", "username", "password");
        Statement stmt = con.createStatement();
        stmt.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255));");
        stmt.execute("INSERT INTO TEST VALUES(1, 'Hello')");
        ResultSet rs = stmt.executeQuery("SELECT * FROM TEST");
        while (rs.next()) {
            assertEquals(1, rs.getInt("ID"));
            assertEquals("Hello", rs.getString("NAME"));
        }
        PreparedStatement pstmt = con.prepareStatement("SELECT * FROM TEST WHERE NAME = ?");
        pstmt.setObject(1, "Hello");
        rs = pstmt.executeQuery();
        while (rs.next()) {
            assertEquals(1, rs.getInt("ID"));
            assertEquals("Hello", rs.getString("NAME"));
        }
    }

    @Test
    public void fetchSize() throws Exception {
        Class.forName("org.h2.Driver");
        Connection con = DriverManager.getConnection("jdbc:h2:mem:test", "username", "password");
        Statement stmt = con.createStatement();
        stmt.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255));");
        for (int i = 0; i < 10000; i++) {
            stmt.execute("INSERT INTO TEST VALUES(" + i + ", 'Hello')");
        }

        PreparedStatement pstmt = con.prepareStatement("SELECT * FROM TEST WHERE NAME = ?");
        pstmt.setObject(1, "Hello");

        // 决定了ResultSet一次加载到内存的数据量
        assertEquals(100, pstmt.getFetchSize());
        ResultSet rs = pstmt.executeQuery();
        while (rs.next()) {
            assertEquals("Hello", rs.getString("NAME"));
        }
    }

    @Test
    public void tx() throws Exception {
        Class.forName("org.h2.Driver");
        Connection con = DriverManager.getConnection("jdbc:h2:mem:test", "username", "password");
        Statement stmt = con.createStatement();
        stmt.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255));");
        stmt.execute("INSERT INTO TEST VALUES(1, 'Hello')");
        con.commit();

        Connection connection1 = DriverManager.getConnection("jdbc:h2:mem:test", "username", "password");
        Connection connection2 = DriverManager.getConnection("jdbc:h2:mem:test", "username", "password");

        Statement statement1 = connection1.createStatement();
        Statement statement2 = connection2.createStatement();

        statement1.execute("UPDATE TEST SET NAME = '1' WHERE ID = 1");
        statement2.execute("UPDATE TEST SET NAME = '2' WHERE ID = 1");

        connection1.commit();
        connection2.commit();

        ResultSet rs = stmt.executeQuery("SELECT * FROM TEST");
        while (rs.next()) {
            assertEquals(1, rs.getInt("ID"));
            assertEquals("2", rs.getString("NAME"));
        }
    }
}
